package marlon.smsbatcheditor.db;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import marlon.smsbatcheditor.Constants;
import marlon.smsbatcheditor.model.Rule;
import marlon.smsbatcheditor.model.RuleAction;
import marlon.smsbatcheditor.model.RuleCondition;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;

/**
 * 
 * @author marlonyao<yaolei135@gmail.com>
 */
public class RuleDAO {
	private static final String TAG = Constants.TAG + "." + RuleDAO.class.getSimpleName();
	
	private static final String DATABASE_NAME = "rules.db";
	private static final int DATABASE_VERSION = 1;
	
	private SQLiteDatabase db;
	
	public RuleDAO(Context context) {
		OpenHelper openHelper = new OpenHelper(context);
		try {
			db = openHelper.getWritableDatabase();
		} catch (SQLiteException e) {
			db = openHelper.getReadableDatabase();
		}
	}
	
	public List<Rule> findAll() {
		Cursor cursor = db.query("rule", new String[] { "_id", "name" }, null, null, null, null, "create_time");
		try {
			List<Rule> rules = new ArrayList<Rule>();
			if (cursor.moveToFirst()) {
				do {
					rules.add(extractRule(cursor));
				} while (cursor.moveToNext());
			}
			return rules;
		} finally {
			cursor.close();
		}
	}

	private Rule extractRule(Cursor cursor) {
		long id = cursor.getLong(0);
		String name = cursor.getString(1);
		Rule rule = new Rule(id, name);
		getConditions(rule);
		getActions(rule);
		return rule;
	}

	public Rule findById(long ruleId) {
		Cursor cursor = db.query("rule", new String[] { "_id", "name" }, "_id="+ruleId, null, null, null, "create_time");
		try {
			if (cursor.moveToFirst()) {
				return extractRule(cursor);
			}
			return null;
		} finally {
			cursor.close();
		}
	}
	
	private void getConditions(Rule rule) {
		Cursor cursor = db.query("rule_condition", new String[] { "term", "predict", "value" }, "rule_id="+rule.getId(), null, null, null, "list_index");
		try {
			if (cursor.moveToFirst()) {
				do {
					RuleCondition cond = null;
					RuleCondition.Term term = RuleCondition.Term.valueOf(cursor.getString(0));
					if (term == RuleCondition.Term.ALL) {
						RuleCondition.Read read = RuleCondition.Read.valueOf(cursor.getString(1));
						if (read == RuleCondition.Read.ALL)
							cond = RuleCondition.all();
						else if (read == RuleCondition.Read.READ)
							cond = RuleCondition.read();
						else if (read == RuleCondition.Read.UNREAD)
							cond = RuleCondition.unread();
					} else {
						RuleCondition.Contains contains = RuleCondition.Contains.valueOf(cursor.getString(1));
						String value = cursor.getString(2);
						cond = new RuleCondition(term, contains, value);
					}
					
					if (cond != null)
						rule.addCondition(cond);
				} while (cursor.moveToNext());
			}
		} finally {
			cursor.close();
		}
	}

	private void getActions(Rule rule) {
		Cursor cursor = db.query("rule_action", new String[] { "type", "value" }, "rule_id="+rule.getId(), null, null, null, "list_index");
		try {
			if (cursor.moveToFirst()) {
				do {
					RuleAction.Type type = RuleAction.Type.valueOf(cursor.getString(0));
					String value = cursor.getString(1);
					rule.addAction(new RuleAction(type, value));
				} while (cursor.moveToNext());
			}
		} finally {
			cursor.close();
		}
	}

	public void insert(Rule rule) {
		db.beginTransaction();
		try {
			long timestamp = timestamp();
			SQLiteStatement stat = db.compileStatement("insert into rule (name, update_time, create_time) values (?, ?, ?)");
			stat.bindString(1, rule.getName());
			stat.bindLong(2, timestamp);
			stat.bindLong(3, timestamp);
			long ruleId = stat.executeInsert();
			rule.setId(ruleId);
			
			insertRuleConds(rule);
			insertRuleActions(rule);
			
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}
	}
	
	private void insertRuleConds(Rule rule) {
		for (int i = 0; i < rule.getConditionCount(); i++) {
			ContentValues values = new ContentValues();
			RuleCondition cond = rule.getCondition(i);
			values.put("rule_id", rule.getId());
			values.put("term", cond.getTerm().name());
			values.put("predict", cond.getPredict().toString());
			values.put("value", cond.getValue());
			values.put("list_index", i);
			db.insert("rule_condition", null, values);
		}
	}
	
	private void insertRuleActions(Rule rule) {
		for (int i = 0; i < rule.getActionCount(); i++) {
			RuleAction action = rule.getAction(i);
			ContentValues values = new ContentValues();
			values.put("rule_id", rule.getId());
			values.put("type", action.getType().name());
			values.put("value", action.getValue());
			values.put("list_index", i);
			db.insert("rule_action", null, values);
		}
	}
	
	public void update(Rule rule) {
		try {
			db.beginTransaction();
			long timestamp = timestamp();
			ContentValues values = new ContentValues();
			values.put("name", rule.getName());
			values.put("update_time", timestamp);
			
			int update = db.update("rule", values, "_id="+rule.getId(), null);
			if (update == 0) {
				throw new IllegalStateException("Cannot update rule");
			}
			
			db.delete("rule_condition", "rule_id="+rule.getId(), null);
			insertRuleConds(rule);
			
			db.delete("rule_action", "rule_id="+rule.getId(), null);
			insertRuleActions(rule);
			
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}
	}
	
	public boolean remove(long ruleId) {
		try {
			db.beginTransaction();
			
			int rows = db.delete("rule", "_id="+ruleId, null);
			if (rows > 0) {
				db.delete("rule_condition", "rule_id="+ruleId, null);
				db.delete("rule_action", "rule_id="+ruleId, null);
			}

			db.setTransactionSuccessful();
			return rows > 0;
		} finally {
			db.endTransaction();
		}
	}
	
	public void save(Rule rule) {
		if (rule.getId() == -1) {
			insert(rule);
		} else {
			update(rule);
		}
	}
	
	private long timestamp() {
		return new Date().getTime();
	}
	
	private static class OpenHelper extends SQLiteOpenHelper {
		public OpenHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}
		
		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL("CREATE TABLE rule (" +
					"_id INTEGER PRIMARY KEY," +
					"name TEXT," +
					"update_time INTEGER," +
					"create_time INTEGER)");
			db.execSQL("CREATE TABLE rule_condition (" +
					"_id INTEGER PRIMARY KEY," +
					"rule_id INTEGER REFERENCES rule," +
					"list_index INTEGER," +
					"term TEXT," +
					"predict TEXT," +
					"value TEXT)");
			db.execSQL("CREATE TABLE rule_action (" +
					"_id INTEGER PRIMARY KEY," +
					"rule_id INTEGER REFERENCES rule," +
					"list_index INTEGER," +
					"type TEXT," +
					"value TEXT)");
			// TODO: create index for rule_id column
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(TAG, "Upgrading database, this will drop tables and recreate.");
			
			db.execSQL("DROP TABLE IF EXISTS rule_condition");
			db.execSQL("DROP TABLE IF EXISTS rule_action");
			db.execSQL("DROP TABLE IF EXISTS rule");
		}
	}

}
